Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


The information contained in the internal tables referenced by these views is used by the optimizer to make decisions about which execution plan to take. The decision about which execution plan to take is also based on information about the size of the object and the data contained in the object. Some of the information contained in these tables is presented in Tables 27.2, 27.3, 27.4, and 27.5.

Table 27.2 Data for Tables in USER_TABLES, ALL_TABLES, DBA_TABLES

Column Description of Contents

AVG_SPACE The average amount of free space in the table.
AVG_ROW_LEN The average length of a row.
BLOCKS The number of blocks in the table.
CHAIN_CNT The number of chained rows.
EMPTY_BLOCKS The number of blocks that have never been used.
NUM_ROWS The number of rows in the table.

Table 27.3 Data for Clusters in USER_CLUSTERS, ALL_CLUSTERS, DBA_CLUSTERS

Column Description of Contents

AVG_BLOCKS_PER_KEY The average number of blocks that have rows that use the same key.
CLUSTER_TYPE The type of cluster: whether it is an index cluster or a hash cluster.
HASHKEYS The number of hash keys if it is a hash cluster.

Table 27.4 Data for Indexes in USER_INDEXES, ALL_INDEXES, DBA_INDEXES

Column Description of Contents

AVG_LEAF_BLOCKS_PER_KEY The average number of leaf blocks (lowest level index blocks) per key.
AVG_DATA_BLOCKS_PER_KEY The average number of data blocks per key.
BLEVEL The level of the B*-Tree.
CLUSTERING_FACTOR The amount of order or disorder in the table the index is referencing.
DISTINCT_KEYS The number of distinct keys in the index.
LEAF_BLOCKS The number of leaf blocks in the index.
UNIQUENESS States whether the index is unique or nonunique.

Table 27.5 Column Data in USER_TAB_COLUMNS, ALL_TAB_COLUMNS, and DBA_TAB_COLUMNS

Column Description of Contents

DENSITY The density of the column (rows per data block).
HIGH_VALUE The second-highest value in this column of the table.
LOW_VALUE The second-lowest value in this column of the table.
NUM_DISTINCT The number of distinct values in this column of the table.

With this information, the optimizer can more precisely determine the optimal execution path based on data about your specific system. When you run the ANALYZE command with the ESTIMATE STATISTICS option, many of these values are estimates rather than actual computed results.

Hints

You can use hints to inform the optimizer of any special facts you know about the data or the SQL statements that may affect the execution plan. By using hints, you indicate that the SQL statement may be more efficient by using a certain execution plan (such as a full-table scan or increased parallelism). Hints are detailed in Chapter 30, “Using Hints.”

Summary

When SQL statements are executed, the Oracle optimizer determines the execution plan based on the available data. The Oracle optimizer uses the optimization approach specified in the initialization parameters to determine the execution plan, as you have seen in this chapter.

The effectiveness of the execution plan depends on the optimization method chosen and the availability of good statistics for your database. When using the cost-based optimization approach, the effectiveness of the optimization can be enhanced by including more and better database performance statistics. Regardless of your system configuration, you can override or enhance the optimization approach by using hints, as discussed in Chapter 30.

This chapter described the various optimization approaches. I recommend the use of the CHOOSE hint under most conditions. When you specify CHOOSE, Oracle takes advantage of the cost-based optimizer under most situations but uses the rule-based approach when no statistics are available. In Chapter 30, you see how SQL statements can be further enhanced by using hints and information you know about your data and your database layout.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.